#ifndef SQLREQUEST_H
#define SQLREQUEST_H

#include <QSqlQuery>
#include <QSqlDatabase>
#include "base/database.h"
#include <QSqlRecord>
#include <QJsonObject>
#include <QJsonDocument>
#include <QJsonArray>

class SqlRequest
{
public:
    SqlRequest();

    QJsonObject tablServerVist();
    QJsonObject tablServerVistEnd();
    int settingsAdd(QMap<QString, QString> settings);
    void connectDB();

    int selNumSud(int socketConn);
    int addDevice(QMap<QString, QString> devices); //Возращает id пульта
    QJsonObject deviceSelect(int idDevice);
    void updateDevice(QMap<QString, int> judges);
    int countJudges(int court);
    QJsonObject startVistup(int system);
    void stopVistup(QJsonObject obj);
    QJsonObject connectJudges(QJsonObject obj);
    int countJudgesPerfomance(int idvist);
    void ocenkiAdd(QJsonObject json);
    void delOcenki(QJsonObject json);
    void delPerfomance(int idvistuplenie);
    void delVist(QJsonObject json);
    QJsonObject ocenkiSelect(int idvist);
    bool prynatGrades(int idvist);
    void insertRange(QMap<QString, QString> range);
    QJsonObject selectRange(QString categoria, int krugNomer);
    QJsonObject selectMesto(QString categoria, int krugNomer);
    int kolPhumKatek(QJsonObject json);
    int countAthlethe(QString category, int krugNomer);
    int countRang(QString category, int krugNomer);
    void phumseServer(QJsonObject obj);
    void flagsServer(QJsonObject obj);
    void updatePerfomance(QJsonObject obj);
    QJsonObject total();
    QJsonObject flagsTotal(int idvist);
    QJsonObject categoriesRang();
    int countKrugCategory(QString category);
    int nomerKrugCategory(QString category);
    QJsonObject krugCategory(QString category);
    void delTwoVistup(int idvistup, int idvistupPred);
 //   QJsonObject selectRange(QString category, int krug);
    QJsonObject selectParticipants(QString categoria, int krugNomer);

private:
    Database *dataBase;
    QSqlQuery query;
    QSqlRecord rec;
    QSqlDatabase mBase;
    QList<int> accuracy, presentation;

    //Запросы для таблицы settings
    const QString addSettings = "INSERT INTO settings (settings_kort, settings_sys, settings_count_judges) VALUES(?, ?, ?)";
    const QString idSettings = "SELECT idsettings FROM settings WHERE idsettings = LAST_INSERT_ID()";
    const QString selectSettings = "SELECT * FROM settings WHERE settings_kort = '";
    const QString updateSettingsStart = "UPDATE settings SET settings_sys = '";
    const QString updateSettingsJudges = "' , settings_cout_judges = '";

    //Запросы для таблицы device
    const QString addDevices = "INSERT INTO device (device_name, device_socketConn) VALUES(?, ?)";
    const QString idDevices = "SELECT iddevice FROM device WHERE iddevice = LAST_INSERT_ID()";
    const QString selectDevicesName = "SELECT iddevice FROM device WHERE device_name = '";
    const QString selectDevices = "SELECT * FROM device WHERE iddevice = ";
    const QString selectSud = "SELECT device_sudNum FROM device WHERE device_socketConn = '";

    const QString updateDevicesStart = "UPDATE device SET device_socketConn = '";
    const QString updateDevicesSud = "' , device_sudNum = '";
    const QString updateDevicesCourt = "' , device_courtNum = '";
    const QString updateDevicesEnd = "' WHERE iddevice = '";
    const QString updateDevicesConnEnd = "' WHERE device_sudNum = '";

    const QString updateDevicesConnectStart = "UPDATE device SET device_sudNum = '";
    const QString updateDevicesConnectCourt = "', device_courtNum = '";
    const QString updateDevicesConnectEnd = "' WHERE device_socketConn = '";

    const QString selectSocket = "SELECT device_socketConn FROM device WHERE device_socketConn != '-1'";

    const QString selectJudges = "SELECT COUNT(*) as judgesCount FROM device WHERE device_courtNum = '"; //Запрос на количество подключенных судей к корту

    //Запросы к таблице vistuplenie
    const QString sqlZapVist = "select idvistuplenie, vistuplenie_nomer, vistuplenie_pokaz, vistuplenie_activno, vistuplenie_sportsmen, "
                               "phumse_name, vistuplenie_kategoria, vistuplenie_pred, vistuplenie_zaversheno, vistuplenie_sort, vistuplenie_krugNomer, phumse_idphumse "
                              "from vistuplenie left outer join phumse on phumse.idphumse=vistuplenie.phumse_idphumse "
                              "where vistuplenie_zaversheno=0 order by vistuplenie_sort, idvistuplenie";

    const QString sqlZapVistEnd = "select idvistuplenie, vistuplenie_nomer, vistuplenie_pokaz, vistuplenie_activno, vistuplenie_sportsmen, "
                               "phumse_name, vistuplenie_kategoria, vistuplenie_pred, vistuplenie_zaversheno, vistuplenie_sort, vistuplenie_krugNomer, phumse_idphumse "
                              "from vistuplenie left outer join phumse on phumse.idphumse=vistuplenie.phumse_idphumse "
                              "order by vistuplenie_sort, vistuplenie_activno, idvistuplenie";

    const QString sqlZapPerfomance = "select idvistuplenie, vistuplenie_nomer, vistuplenie_pokaz, vistuplenie_activno, vistuplenie_sportsmen, "
                               "phumse_name, vistuplenie_kategoria, vistuplenie_pred, vistuplenie_zaversheno, vistuplenie_sort, vistuplenie_krugNomer, phumse_idphumse "
                              "from vistuplenie left outer join phumse on phumse.idphumse=vistuplenie.phumse_idphumse "
                              "order by vistuplenie_zaversheno desc";

    const QString sqlZapID = "select idvistuplenie, vistuplenie_nomer, vistuplenie_pokaz, vistuplenie_sportsmen, phumse_name, vistuplenie_kategoria, vistuplenie_pred, "
                             "phumse_idphumse, vistuplenie_sort, vistuplenie_krugNomer "
                              "from vistuplenie left outer join phumse on phumse.idphumse=vistuplenie.phumse_idphumse "
                              "where (vistuplenie_activno=0 or vistuplenie_activno=1) and vistuplenie_zaversheno=0 order by vistuplenie_sort, idvistuplenie limit 1";
    const QString sqlSelectAthletheStart = "SELECT COUNT(*) as countAthlethe FROM vistuplenie WHERE vistuplenie_kategoria = '";
    const QString sqlSelectAthletheEnd = "' AND vistuplenie_krugNomer = ";
    const QString addChelVist = "INSERT INTO vistuplenie (vistuplenie_nomer, vistuplenie_pokaz, vistuplenie_activno, vistuplenie_zaversheno, "
                                "vistuplenie_sportsmen, phumse_idphumse, vistuplenie_kategoria, vistuplenie_pred, vistuplenie_krugNomer) "
                                "VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?)";
    const QString addChelVistLast = "SELECT idvistuplenie FROM vistuplenie WHERE idvistuplenie = LAST_INSERT_ID()";
    const QString selectKrugVistStart = "SELECT vistuplenie_krugNomer FROM vistuplenie WHERE (vistuplenie_zaversheno=0 or vistuplenie_zaversheno=1) and vistuplenie_kategoria = '";
    const QString selectKrugVistEnd = "' limit 1";

    const QString sqlUpdVistAc = "UPDATE vistuplenie SET vistuplenie_activno = ";
    const QString sqlUpdVistZa = " , vistuplenie_zaversheno = ";
    const QString sqlUpdVistSo = " , vistuplenie_sort = ";
    const QString sqlUpdVistPo = " , vistuplenie_pokaz = ";
    const QString sqlUpdVistSp = " , vistuplenie_sportsmen = '";
    const QString sqlUpdVistID = "' WHERE idvistuplenie = ";

    const QString sqlDelPerfomance = "DELETE FROM vistuplenie WHERE idvistuplenie = ";
    const QString sqlDelVist = "DELETE FROM vistuplenie WHERE idvistuplenie = '";
    const QString sqlDelVistSelect = "SELECT vistuplenie_pred FROM vistuplenie where idvistuplenie = '";

     //Запрос для вывода выступлений
    const QString sqlUpdStartVist = "UPDATE vistuplenie SET vistuplenie_activno=1 WHERE idvistuplenie='"; //Запрос для запуска выступления
    const QString sqlZaverVist = "UPDATE vistuplenie SET vistuplenie_activno=0, vistuplenie_zaversheno=1 WHERE idvistuplenie = '"; //Запрос для завершения выступления
    const QString sqlCountPhumseStart = "SELECT COUNT(*) as kolPhum from vistuplenie where vistuplenie_zaversheno = 0 and vistuplenie_nomer = '"; //Запрос подсчёта количества пхумсэ в категории
    const QString sqlCountPhumse = "' and vistuplenie_krugNomer = '"; //Запрос подсчёта количества пхумсэ в категории
    const QString sqlCountPhumseEnd = "' and vistuplenie_kategoria = '"; //Запрос подсчёта количества пхумсэ в категории
    const QString sqlUpdStopVist = "UPDATE vistuplenie SET vistuplenie_activno=0 WHERE vistuplenie_zaversheno = 0 AND idvistuplenie='"; //Запрос для запуска выступления

    //Запрос для таблицы sudgotov
    const QString addJudges = "INSERT INTO sudgotov (vistuplenie_idvistuplenie, nomersud) VALUES (?, ?)";
    const QString selectJudgesPerfomance = "SELECT nomersud FROM sudgotov WHERE vistuplenie_idvistuplenie = '";
    const QString selectJudgesStart = "SELECT nomersud FROM sudgotov WHERE nomersud = '";
    const QString selectJudgesEnd = "' AND vistuplenie_idvistuplenie = '";
    const QString updateJudgesStart = "UPDATE sudgotov SET nomersud = '";
    const QString updateJudgesEnd = "' WHERE vistuplenie_idvistuplenie = '";
    const QString selectCountJudges = "SELECT COUNT(*) as judgesCount FROM sudgotov WHERE vistuplenie_idvistuplenie = '";

    //Запросы к таблицу ocenki
    const QString addOcenki = "INSERT INTO ocenki (vistuplenie_idvistuplenie, ocenki_nomsud, ocenki_t, ocenki_p) VALUES (?, ?, ?, ?)";
    const QString updateOcenkiStart = "UPDATE ocenki SET ocenki_nomsud = '";
    const QString updateOcenkiT = "', ocenki_t = '";
    const QString updateOcenkiP = "', ocenki_p = '";
    const QString updateOcenkiEnd = "' WHERE vistuplenie_idvistuplenie = '";
    const QString selectOcenki = "SELECT * FROM ocenki WHERE vistuplenie_idvistuplenie = '";
    const QString selectCountOcenki = "SELECT COUNT(*) as ocenkiCount FROM ocenki WHERE vistuplenie_idvistuplenie = '";
    const QString delOcenkiStart = "DELETE FROM ocenki WHERE vistuplenie_idvistuplenie = '";
    const QString delOcenkiEnd = "' AND ocenki_nomsud = '";

    //Запрос к таблице rang
    const QString addRang = "INSERT INTO rang (vistuplenie_idvistuplenie, rang_tot1, rang_tot2, rang_tot, rang_vistplenie_pred, rang_sumteh, rang_sumpres, "
                              " rang_sumobsh, rang_category, rang_athlethe_nomer, rang_athlethe, rang_tekKrug) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
    const QString selectRangStart = "SELECT * FROM rang WHERE rang_category = '";
    const QString selectRang = "' AND rang_tekKrug = '";
    const QString selectRangEnd = "' order by rang_tot DESC, rang_sumpres DESC, rang_sumobsh DESC";
    const QString sqlSelectMestoStart = "SELECT ROW_NUMBER() OVER(ORDER BY rang_tot DESC, "
                                     "rang_sumpres DESC, "
                                     "rang_sumobsh DESC) AS mesto, vistuplenie_idvistuplenie FROM rang "
                                     "WHERE rang_category = '";
    const QString sqlSelectMestoEnd = "' AND rang_tekKrug = '";

    const QString sqlSelectRangStart = "SELECT COUNT(*) as countRang FROM rang WHERE rang_category = '";
    const QString sqlSelectRangEnd = "' AND rang_tekKrug = ";

    const QString sqlSelectCategoties = "SELECT group_concat(distinct rang_category) as rang_category FROM apulti.rang group by rang_category";
    const QString sqlKrugCountStart = "SELECT COUNT(*) as countKrug FROM "
                                             "(SELECT group_concat(distinct rang_category) as rang_category, group_concat(distinct rang_tekKrug) as rang_tekKrug "
                                             "FROM rang WHERE rang_category ='";
    const QString sqlKrugCountEnd = "' group by rang_category, rang_tekKrug) as T;";

    const QString sqlParticipantsSelectSrat = "SELECT vistuplenie_nomer, vistuplenie_sportsmen"
            " FROM apulti.vistuplenie"
            " where vistuplenie_kategoria = '";
    const QString sqlParticipantsSelect = "' and vistuplenie_krugNomer = '";
    const QString sqlParticipantsSelectEnd = "' group by vistuplenie_nomer, vistuplenie_sportsmen";

    const QString sqlNomerKrugSrat = "SELECT vistuplenie_krugNomer"
            " FROM apulti.vistuplenie"
            " where vistuplenie_kategoria = '";
    const QString sqlNomerKrugEnd = "' AND vistuplenie_zaversheno = 1 group by vistuplenie_krugNomer";

    int catInt = 0;
};

#endif // SQLREQUEST_H
